<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      db/MySQL系列/13-MySQL系列之-优化 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>db/MySQL系列/13-MySQL系列之-优化</h2>



  <p class="post-date">2019-12-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="1-优化哲学"><a href="#1-优化哲学" class="headerlink" title="1.优化哲学"></a>1.优化哲学</h1><h2 id="1-1-为什么优化？"><a href="#1-1-为什么优化？" class="headerlink" title="1.1 为什么优化？"></a>1.1 为什么优化？</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">为了获得成就感?</span><br><span class="line">为了证实比系统设计者更懂数据库?</span><br><span class="line">为了从优化成果来证实优化者更有价值?</span><br></pre></td></tr></table></figure>

<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-07ec8ccb021ce95b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/208/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">但通常事实证实的结果往往会和您期待相反！</span><br><span class="line">优化有风险，涉足需谨慎！</span><br></pre></td></tr></table></figure>

<h2 id="1-2-优化风险"><a href="#1-2-优化风险" class="headerlink" title="1.2 优化风险"></a>1.2 优化风险</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">优化不总是对一个单纯的环境进行！还很可能是一个复杂的已投产的系统。</span><br><span class="line">优化手段本来就有很大的风险，只不过你没能力意识到和预见到！</span><br><span class="line">任何的技术可以解决一个问题，但必然存在带来一个问题的风险！</span><br><span class="line">对于优化来说解决问题而带来的问题控制在可接受的范围内才是有成果。</span><br><span class="line">保持现状或出现更差的情况都是失败！</span><br><span class="line"></span><br><span class="line">稳定性和业务可持续性通常比性能更重要！</span><br><span class="line">优化不可避免涉及到变更，变更就有风险！</span><br><span class="line">优化使性能变好，维持和变差是等概率事件！</span><br><span class="line">优化不能只是数据库管理员担当风险，但会所有的人分享优化成果！</span><br><span class="line">所以优化工作是由业务需要驱使的！！！</span><br></pre></td></tr></table></figure>

<h2 id="1-3-谁参与优化"><a href="#1-3-谁参与优化" class="headerlink" title="1.3 谁参与优化"></a>1.3 谁参与优化</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">数据库管理员</span><br><span class="line">业务部门代表</span><br><span class="line">应用程序架构师</span><br><span class="line">应用程序设计人员</span><br><span class="line">应用程序开发人员</span><br><span class="line">硬件及系统管理员</span><br><span class="line">存储管理员</span><br></pre></td></tr></table></figure>

<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-76d310bb00d62d3e.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/361/format/webp" alt="img"></p>
<p>image.png</p>
<h2 id="1-4-优化方向"><a href="#1-4-优化方向" class="headerlink" title="1.4 优化方向"></a>1.4 优化方向</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">安全优化（业务持续性）</span><br><span class="line">性能优化（业务高效性）</span><br></pre></td></tr></table></figure>

<h2 id="1-5-优化的范围及思路"><a href="#1-5-优化的范围及思路" class="headerlink" title="1.5 优化的范围及思路"></a>1.5 优化的范围及思路</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">优化范围：</span><br><span class="line">存储、主机和操作系统:</span><br><span class="line">    主机架构稳定性</span><br><span class="line">    I/O规划及配置</span><br><span class="line">    Swap</span><br><span class="line">    OS内核参数</span><br><span class="line">        网络问题</span><br><span class="line">应用程序:（Index，<span class="keyword">lock</span>，session）</span><br><span class="line">        应用程序稳定性和性能</span><br><span class="line">        SQL语句性能</span><br><span class="line">    串行访问资源</span><br><span class="line">    性能欠佳会话管理</span><br><span class="line">数据库优化:（内存、数据库设计、参数）</span><br><span class="line">    内存</span><br><span class="line">    数据库结构(物理&amp;逻辑)</span><br><span class="line">    实例配置</span><br></pre></td></tr></table></figure>

<h2 id="优化效果和成本的评估："><a href="#优化效果和成本的评估：" class="headerlink" title="优化效果和成本的评估："></a>优化效果和成本的评估：</h2><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-678232e9db29674c.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/816/format/webp" alt="img"></p>
<p>image.png</p>
<h1 id="2-优化工具的使用"><a href="#2-优化工具的使用" class="headerlink" title="2. 优化工具的使用"></a>2. 优化工具的使用</h1><h2 id="2-1-系统层面的"><a href="#2-1-系统层面的" class="headerlink" title="2.1 系统层面的"></a>2.1 系统层面的</h2><h3 id="2-1-1-CPU"><a href="#2-1-1-CPU" class="headerlink" title="2.1.1 CPU"></a>2.1.1 CPU</h3><h3 id="top"><a href="#top" class="headerlink" title="top"></a>top</h3><h3 id="cpu使用情况的平均值："><a href="#cpu使用情况的平均值：" class="headerlink" title="cpu使用情况的平均值："></a>cpu使用情况的平均值：</h3><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-5a9ab6361f5bd39b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/882/format/webp" alt="img"></p>
<p>image.png</p>
<h3 id="CPU每个核心的分别使用的情况（按1）："><a href="#CPU每个核心的分别使用的情况（按1）：" class="headerlink" title="CPU每个核心的分别使用的情况（按1）："></a>CPU每个核心的分别使用的情况（按1）：</h3><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-79cd367c7f5cc972.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/880/format/webp" alt="img"></p>
<p>image.png</p>
<h3 id="程序是如何使用CPU的？"><a href="#程序是如何使用CPU的？" class="headerlink" title="程序是如何使用CPU的？"></a>程序是如何使用CPU的？</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">系统给每个程序分配CPU的时候，以时间来划分表的。</span><br></pre></td></tr></table></figure>

<h3 id="CPU有效工作时间"><a href="#CPU有效工作时间" class="headerlink" title="CPU有效工作时间?"></a>CPU有效工作时间?</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">计算: 程序运行,数据处理</span><br><span class="line">控制: 少量的关于申请资源和释放资源等</span><br></pre></td></tr></table></figure>

<h3 id="CPU无效工作时间"><a href="#CPU无效工作时间" class="headerlink" title="CPU无效工作时间"></a>CPU无效工作时间</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">等待 IO</span><br></pre></td></tr></table></figure>

<h3 id="CPU各项指标说明"><a href="#CPU各项指标说明" class="headerlink" title="CPU各项指标说明"></a>CPU各项指标说明</h3><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">0<span class="selector-class">.0</span> <span class="selector-tag">us</span></span><br><span class="line">用户程序，在运行过程中，使用的<span class="selector-tag">CPU</span>时间的占比。</span><br><span class="line">我们希望的是越高越好，尽量控制在90%</span><br><span class="line">0<span class="selector-class">.0</span> <span class="selector-tag">sy</span></span><br><span class="line">控制: 资源管理，内核的工作(系统调用)</span><br><span class="line"><span class="selector-tag">sys</span>高的原因: </span><br><span class="line">      1.  <span class="selector-tag">bug</span> ,中病毒了</span><br><span class="line">      2.  锁的问题</span><br><span class="line">99<span class="selector-class">.9</span> <span class="selector-tag">id</span> </span><br><span class="line"><span class="selector-tag">CPU</span>空间的时间占比      </span><br><span class="line"></span><br><span class="line">0<span class="selector-class">.0</span> <span class="selector-tag">wa</span></span><br><span class="line"><span class="selector-tag">CPU</span>花在等待上的时间</span><br><span class="line"><span class="selector-tag">wa</span>高的原因：</span><br><span class="line">          1. 锁</span><br><span class="line">          2. <span class="selector-tag">IO</span> （<span class="selector-tag">raid</span>，过度条带化）</span><br><span class="line">          3. 索引</span><br><span class="line">多<span class="selector-tag">cpu</span>使用情况监控：</span><br><span class="line">主要判断我们<span class="selector-tag">cpu</span>多核心有没有被充分利用。</span><br><span class="line">现象：单颗很忙，其他很闲，对于<span class="selector-tag">MySQL</span>来讲，有可能是并发参数设定不合理导致的。</span><br></pre></td></tr></table></figure>

<h2 id="2-1-2-MEM"><a href="#2-1-2-MEM" class="headerlink" title="2.1.2 MEM"></a>2.1.2 MEM</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">KiB Mem :  <span class="number">4028432</span> total,  <span class="number">3774464</span> <span class="built_in">free</span>,   <span class="number">116240</span> used,   <span class="number">137728</span> buff/cache</span><br><span class="line">### <span class="number">1.2</span><span class="number">.1</span> 名称介绍</span><br><span class="line">total :总内存大小</span><br><span class="line"><span class="built_in">free</span>  ：空闲的</span><br><span class="line">used  ：在使用的</span><br><span class="line">buff/cache ：缓冲区 和 缓存</span><br></pre></td></tr></table></figure>

<h3 id="2-1-3-内存管理子系统："><a href="#2-1-3-内存管理子系统：" class="headerlink" title="2.1.3 内存管理子系统："></a>2.1.3 内存管理子系统：</h3><figure class="highlight swift"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">slab <span class="type">Allocator</span></span><br><span class="line">buddy system </span><br><span class="line">程序=指令+数据</span><br><span class="line">对于page cache来讲(<span class="type">OS</span> buffer)</span><br><span class="line"><span class="number">1</span>. 内存的可用空间的计算   free +buffer cache </span><br><span class="line"><span class="number">2</span>. 内存回收(buffer)的方式:</span><br><span class="line">        (<span class="number">1</span>) 写入磁盘</span><br><span class="line">        (<span class="number">2</span>) <span class="built_in">swap</span>  </span><br><span class="line">对于数据库来讲：需要将<span class="built_in">swap</span>屏蔽掉</span><br></pre></td></tr></table></figure>

<h2 id="2-1-4-swap"><a href="#2-1-4-swap" class="headerlink" title="2.1.4 swap"></a>2.1.4 swap</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">KiB <span class="symbol">Swap:</span>  <span class="number">2097148</span> total,  <span class="number">2097148</span> free,        <span class="number">0</span> used.  <span class="number">3701464</span> avail Mem </span><br><span class="line">Linux <span class="number">6</span>操作系统，默认回收策略（buffer cache），不立即回收策略</span><br><span class="line">内存使用达到<span class="number">100</span><span class="string">%-60%时候，40% 会使用swap</span></span><br><span class="line"><span class="string">Linux 7操作系统</span></span><br><span class="line"><span class="string">内存使用达到100%-</span><span class="number">30</span>%（<span class="number">70</span>%）时候，才会时候swap</span><br><span class="line">cat /proc/sys/vm/swappiness </span><br><span class="line"><span class="number">30</span>  </span><br><span class="line">echo <span class="number">0</span> &gt;<span class="regexp">/proc/sys</span><span class="regexp">/vm/swappiness</span>    的内容改成<span class="number">0</span>（临时）</span><br><span class="line">vim /etc/sysctl.conf</span><br><span class="line">添加<span class="symbol">:</span></span><br><span class="line">vm.swappiness=<span class="number">0</span></span><br><span class="line">sysctl -p</span><br></pre></td></tr></table></figure>

<h3 id="2-2-iostat-命令"><a href="#2-2-iostat-命令" class="headerlink" title="2.2. iostat 命令"></a>2.2. iostat 命令</h3><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">dd <span class="keyword">if</span>=<span class="regexp">/dev/zero</span> of=<span class="regexp">/tmp/bigfile</span> bs=<span class="number">1</span>M count=<span class="number">4096</span></span><br><span class="line">iostat -dm <span class="number">1</span></span><br><span class="line">现象说明</span><br><span class="line"><span class="number">1</span>. IO 高 cpu us 也高,属于正常现象</span><br><span class="line"><span class="number">2</span>. CPU  us高  IO很低   ,MySQL 不在做增删改查,有可能是存储过程,函数,排序,分组,多表连接</span><br><span class="line"><span class="number">3</span>. Wait,SYS 高  , IO低<span class="symbol">:IO</span>出问题了,锁等待过多的几率比较大. </span><br><span class="line">IOPS：每秒磁盘最多能够发生的IO次数，这是个定值 </span><br><span class="line">频繁小事务,IOPS很高,达到阈值,可能IO吞吐量没超过IO最大吞吐量.无法新的IO了</span><br><span class="line">存储规划有问题.</span><br></pre></td></tr></table></figure>

<h2 id="2-3-数据库优化工具"><a href="#2-3-数据库优化工具" class="headerlink" title="2.3 数据库优化工具"></a>2.3 数据库优化工具</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> status  </span><br><span class="line"><span class="keyword">show</span> variables </span><br><span class="line"><span class="keyword">show</span> index  </span><br><span class="line"><span class="keyword">show</span> processlist </span><br><span class="line"><span class="keyword">show</span> slave status</span><br><span class="line"><span class="keyword">show</span> engine innodb status </span><br><span class="line">desc /explain </span><br><span class="line">    slowlog</span><br><span class="line">扩展类深度优化:</span><br><span class="line">pt系列</span><br><span class="line">mysqlslap </span><br><span class="line">sysbench </span><br><span class="line">information_schema </span><br><span class="line">performance_schema</span><br><span class="line">sys</span><br></pre></td></tr></table></figure>

<h1 id="3-优化思路分解"><a href="#3-优化思路分解" class="headerlink" title="3. 优化思路分解"></a>3. 优化思路分解</h1><h2 id="3-1-硬件优化"><a href="#3-1-硬件优化" class="headerlink" title="3.1 硬件优化"></a>3.1 硬件优化</h2><h2 id="主机"><a href="#主机" class="headerlink" title="主机"></a>主机</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">真实的硬件（PC Server）: DELL  R系列 ，华为，浪潮，HP，联想</span><br><span class="line">云产品：ECS、数据库RDS、DRDS</span><br><span class="line">IBM 小型机 P6  570  595   P7 720  750 780     P8</span><br></pre></td></tr></table></figure>

<h2 id="CPU根据数据库类型"><a href="#CPU根据数据库类型" class="headerlink" title="CPU根据数据库类型"></a>CPU根据数据库类型</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">OLTP </span><br><span class="line">OLAP  </span><br><span class="line">IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发</span><br><span class="line">CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）</span><br><span class="line">CPU密集型： I 系列的，主频很高，核心少 </span><br><span class="line">IO密集型：  E系列（至强），主频相对低，核心数量多</span><br></pre></td></tr></table></figure>

<h2 id="内存"><a href="#内存" class="headerlink" title="内存"></a>内存</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">建议2-3倍cpu核心数量 （ECC）</span><br></pre></td></tr></table></figure>

<h2 id="磁盘选择"><a href="#磁盘选择" class="headerlink" title="磁盘选择"></a>磁盘选择</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SATA-III   SAS    Fc    SSD（sata） pci<span class="_">-e</span>  ssd  Flash</span><br><span class="line">主机 RAID卡的BBU(Battery Backup Unit)关闭</span><br></pre></td></tr></table></figure>

<h2 id="存储"><a href="#存储" class="headerlink" title="存储"></a>存储</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">根据存储数据种类的不同，选择不同的存储设备</span><br><span class="line">配置合理的RAID级别(raid5、raid10、热备盘)   </span><br><span class="line">r0 :条带化 ,性能高</span><br><span class="line">r1 :镜像，安全</span><br><span class="line">r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）</span><br><span class="line">r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）</span><br></pre></td></tr></table></figure>

<h2 id="网络"><a href="#网络" class="headerlink" title="网络"></a>网络</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">1、硬件买好的（单卡单口）</span><br><span class="line">2、网卡绑定（bonding），交换机堆叠</span><br><span class="line">以上问题，提前规避掉。</span><br></pre></td></tr></table></figure>

<h2 id="3-2-操作系统优化"><a href="#3-2-操作系统优化" class="headerlink" title="3.2 操作系统优化"></a>3.2 操作系统优化</h2><h2 id="Swap调整"><a href="#Swap调整" class="headerlink" title="Swap调整"></a>Swap调整</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">echo</span> 0 &gt;/proc/sys/vm/swappiness的内容改成0（临时），</span><br><span class="line">/etc/sysctl.conf</span><br><span class="line">上添加vm.swappiness=0（永久）</span><br><span class="line">sysctl -p</span><br><span class="line"></span><br><span class="line">这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。</span><br><span class="line">当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。</span><br><span class="line"></span><br><span class="line">修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式</span><br><span class="line">这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo <span class="built_in">log</span>依旧会使用文件系统cache。值得注意的是，Redo <span class="built_in">log</span>是覆写模式的，即使使用了文件系统的cache，也不会占用太多</span><br></pre></td></tr></table></figure>

<h2 id="IO调度策略"><a href="#IO调度策略" class="headerlink" title="IO调度策略"></a>IO调度策略</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">centos 7 默认是deadline</span><br><span class="line">cat   /sys/block/sda/queue/scheduler</span><br><span class="line"></span><br><span class="line"><span class="comment">#临时修改为deadline(centos6)</span></span><br><span class="line"><span class="built_in">echo</span> deadline &gt;/sys/block/sda/queue/scheduler </span><br><span class="line">vi /boot/grub/grub.conf</span><br><span class="line">更改到如下内容:</span><br><span class="line">kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet</span><br><span class="line"></span><br><span class="line">IO ：</span><br><span class="line">    raid</span><br><span class="line">    no lvm</span><br><span class="line">    ext4或xfs</span><br><span class="line">    ssd</span><br><span class="line">    IO调度策略</span><br><span class="line">提前规划好以上所有问题，减轻MySQL优化的难度。</span><br></pre></td></tr></table></figure>

<h2 id="3-3-应用端"><a href="#3-3-应用端" class="headerlink" title="3.3 应用端"></a>3.3 应用端</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">1. 开发过程规范,标准</span><br><span class="line">2. 减少烂SQL:不走索引,复杂逻辑,切割大事务.</span><br><span class="line">3. 避免业务逻辑错误,避免锁争用.</span><br><span class="line">这个阶段,需要我们DBA深入业务,或者要和开发人员\业务人员配合实现</span><br><span class="line"></span><br><span class="line">优化,最根本的是<span class="string">"优化"</span>人.</span><br><span class="line">                    ----oldguo</span><br></pre></td></tr></table></figure>

<h1 id="4-MySQL参数优化测试"><a href="#4-MySQL参数优化测试" class="headerlink" title="4. MySQL参数优化测试"></a>4. MySQL参数优化测试</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">虚拟机vm12<span class="number">.5</span>，OS centos <span class="number">6.9</span>（系统已优化），cpu*<span class="number">4</span>（I5 <span class="number">4440</span> <span class="number">3.1</span>GHZ）,MEM*<span class="number">4</span>GB ,HardDisk:SSD</span><br><span class="line"></span><br><span class="line">模拟数据库数据</span><br><span class="line">drop database <span class="keyword">if</span> exists oldboy;</span><br><span class="line">create database oldboy charset utf8mb4 collate utf8mb4_bin;</span><br><span class="line">use oldboy;</span><br><span class="line"><span class="function">create table <span class="title">t_100w</span> (<span class="params">id <span class="keyword">int</span>,num <span class="keyword">int</span>,k1 <span class="keyword">char</span>(<span class="number">2</span></span>),k2 <span class="title">char</span>(<span class="params"><span class="number">4</span></span>),dt timestamp)</span>;</span><br><span class="line">delimiter <span class="comment">//</span></span><br><span class="line"><span class="function">create  procedure <span class="title">rand_data</span>(<span class="params"><span class="keyword">in</span> num <span class="keyword">int</span></span>)</span></span><br><span class="line"><span class="function">begin</span></span><br><span class="line"><span class="function">declare str <span class="title">char</span>(<span class="params"><span class="number">62</span></span>) <span class="keyword">default</span> 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'</span>;</span><br><span class="line"><span class="function">declare str2 <span class="title">char</span>(<span class="params"><span class="number">2</span></span>)</span>;</span><br><span class="line"><span class="function">declare str4 <span class="title">char</span>(<span class="params"><span class="number">4</span></span>)</span>;</span><br><span class="line">declare i <span class="keyword">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"><span class="keyword">while</span> i&lt;num <span class="keyword">do</span></span><br><span class="line"><span class="keyword">set</span> str2=concat(substring(str,<span class="number">1</span>+floor(rand()*<span class="number">61</span>),<span class="number">1</span>),substring(str,<span class="number">1</span>+floor(rand()*<span class="number">61</span>),<span class="number">1</span>));</span><br><span class="line"><span class="keyword">set</span> str4=concat(substring(str,<span class="number">1</span>+floor(rand()*<span class="number">61</span>),<span class="number">2</span>),substring(str,<span class="number">1</span>+floor(rand()*<span class="number">61</span>),<span class="number">2</span>));</span><br><span class="line"><span class="keyword">set</span> i=i+<span class="number">1</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> t_100w <span class="title">values</span> (<span class="params">i,floor(rand(</span>)*num),str2,str4,<span class="title">now</span>(<span class="params"></span>))</span>;</span><br><span class="line">end <span class="keyword">while</span>;</span><br><span class="line">end;</span><br><span class="line"><span class="comment">//</span></span><br><span class="line">delimiter ;</span><br><span class="line">插入<span class="number">100</span>w条数据：</span><br><span class="line"><span class="function">call <span class="title">rand_data</span>(<span class="params"><span class="number">10000000</span></span>)</span>;</span><br><span class="line">commit;</span><br><span class="line"></span><br><span class="line">mysqlslap --defaults-file=/etc/my.cnf \</span><br><span class="line">--concurrency=<span class="number">100</span> --iterations=<span class="number">1</span> --create-schema=<span class="string">'oldboy'</span> \</span><br><span class="line">--query=<span class="string">"select * from oldboy.t1000w where k2='FGCD'"</span> engine=innodb \</span><br><span class="line">--number-of-queries=<span class="number">200</span> -uroot -p123 -verbose</span><br></pre></td></tr></table></figure>

<h1 id="5-优化细节："><a href="#5-优化细节：" class="headerlink" title="5. 优化细节："></a>5. 优化细节：</h1><h2 id="5-1-参数优化"><a href="#5-1-参数优化" class="headerlink" title="5.1 参数优化"></a>5.1 参数优化</h2><h2 id="Max-connections"><a href="#Max-connections" class="headerlink" title="Max_connections *****"></a>Max_connections *****</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介</span><br><span class="line">Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。</span><br><span class="line">（<span class="number">2</span>）判断依据</span><br><span class="line">show variables like <span class="string">'max_connections'</span>;</span><br><span class="line">    +-----------------+-------+</span><br><span class="line">    <span class="params">| Variable_name   |</span> Value <span class="params">|</span></span><br><span class="line"><span class="params">    +-----------------+-------+</span></span><br><span class="line"><span class="params">    |</span> max_connections <span class="params">| 151   |</span></span><br><span class="line">    +-----------------+-------+</span><br><span class="line">show status like <span class="string">'Max_used_connections'</span>;</span><br><span class="line">    +----------------------+-------+</span><br><span class="line">    <span class="params">| Variable_name        |</span> Value <span class="params">|</span></span><br><span class="line"><span class="params">    +----------------------+-------+</span></span><br><span class="line"><span class="params">    |</span> Max_used_connections <span class="params">| 101   |</span></span><br><span class="line">    +----------------------+-------+</span><br><span class="line"></span><br><span class="line">（<span class="number">3</span>）修改方式举例</span><br><span class="line">vim /etc/my.cnf </span><br><span class="line">Max_connections=<span class="number">1024</span></span><br><span class="line"></span><br><span class="line">补充<span class="symbol">:</span></span><br><span class="line">    <span class="number">1</span>.开启数据库时,我们可以临时设置一个比较大的测试值</span><br><span class="line">    <span class="number">2</span>.观察show status like <span class="string">'Max_used_connections'</span>;变化</span><br><span class="line">    <span class="number">3</span>.如果max_used_connections跟max_connections相同,</span><br><span class="line">    那么就是max_connections设置过低或者超过服务器的负载上限了，</span><br><span class="line">    低于<span class="number">10</span>%则设置过大.</span><br></pre></td></tr></table></figure>

<h2 id="back-log"><a href="#back-log" class="headerlink" title="back_log ***"></a>back_log ***</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介</span><br><span class="line">mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。</span><br><span class="line">back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它</span><br><span class="line">（<span class="number">2</span>）判断依据</span><br><span class="line"><span class="keyword">show</span> full processlist</span><br><span class="line">发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值</span><br><span class="line">（<span class="number">3</span>）修改方式举例</span><br><span class="line">vim /etc/my.cnf </span><br><span class="line">back_log=<span class="number">1024</span></span><br></pre></td></tr></table></figure>

<h2 id="wait-timeout和interactive-timeout"><a href="#wait-timeout和interactive-timeout" class="headerlink" title="wait_timeout和interactive_timeout ****"></a>wait_timeout和interactive_timeout ****</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介</span><br><span class="line">wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数</span><br><span class="line">interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是<span class="number">28800</span>，可调优为<span class="number">7200</span>。</span><br><span class="line">wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用</span><br><span class="line">（<span class="number">2</span>）设置建议</span><br><span class="line">如果设置太大，容易造成连接打开时间过长，在<span class="keyword">show</span> processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低</span><br><span class="line">（<span class="number">3</span>）修改方式举例</span><br><span class="line">wait_timeout=<span class="number">60</span></span><br><span class="line">interactive_timeout=<span class="number">1200</span></span><br><span class="line">长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。</span><br><span class="line">一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。</span><br><span class="line">另外还可以使用类外的参数弥补。</span><br></pre></td></tr></table></figure>

<h2 id="5-4-key-buffer-size"><a href="#5-4-key-buffer-size" class="headerlink" title="5.4 key_buffer_size *****"></a>5.4 key_buffer_size *****</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介</span><br><span class="line">key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度</span><br><span class="line">《<span class="number">1</span>》此参数与myisam表的索引有关</span><br><span class="line">《<span class="number">2</span>》临时表的创建有关（多表链接、子查询中、union）</span><br><span class="line">     在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃</span><br><span class="line">     临时表有两种创建方式：</span><br><span class="line">                        内存中-------&gt;key_buffer_size</span><br><span class="line">                        磁盘上-------&gt;ibdata1(<span class="number">5.6</span>)</span><br><span class="line">                                      ibtmp1 (<span class="number">5.7</span>）</span><br><span class="line">（<span class="number">2</span>）设置依据</span><br><span class="line">通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。</span><br><span class="line">mysql&gt; <span class="keyword">show</span> variables like <span class="string">"key_buffer_size%"</span>;</span><br><span class="line">+-----------------+---------+</span><br><span class="line">| Variable_name   | Value   |</span><br><span class="line">+-----------------+---------+</span><br><span class="line">| key_buffer_size | <span class="number">8388608</span> |</span><br><span class="line">+-----------------+---------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; </span><br><span class="line">mysql&gt; <span class="keyword">show</span> status like <span class="string">"key_read%"</span>;</span><br><span class="line">+-------------------+-------+</span><br><span class="line">| Variable_name     | Value |</span><br><span class="line">+-------------------+-------+</span><br><span class="line">| Key_read_requests | <span class="number">10</span>    |</span><br><span class="line">| Key_reads         | <span class="number">2</span>     |</span><br><span class="line">+-------------------+-------+</span><br><span class="line"><span class="number">2</span> rows <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; </span><br><span class="line">一共有<span class="number">10</span>个索引读取请求，有<span class="number">2</span>个请求在内存中没有找到直接从硬盘中读取索引</span><br><span class="line">控制在 <span class="number">5</span>%以内 。</span><br><span class="line">注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。</span><br><span class="line">可以使用检查状态值created_tmp_disk_tables得知：</span><br><span class="line"></span><br><span class="line">mysql&gt; <span class="keyword">show</span> status like <span class="string">"created_tmp%"</span>;</span><br><span class="line">+-------------------------+-------+</span><br><span class="line">| Variable_name           | Value |</span><br><span class="line">+-------------------------+-------+</span><br><span class="line">| Created_tmp_disk_tables | <span class="number">0</span>     |</span><br><span class="line">| Created_tmp_files       | <span class="number">6</span>     |</span><br><span class="line">| Created_tmp_tables      | <span class="number">1</span>     |</span><br><span class="line">+-------------------------+-------+</span><br><span class="line"><span class="number">3</span> rows <span class="keyword">in</span> <span class="keyword">set</span> (<span class="number">0.00</span> sec)</span><br><span class="line">mysql&gt; </span><br><span class="line">通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) </span><br><span class="line">Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) </span><br><span class="line"></span><br><span class="line">或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。</span><br><span class="line">Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) </span><br><span class="line">控制在<span class="number">5</span>%<span class="number">-10</span>%以内</span><br><span class="line">看以下例子：</span><br><span class="line">在调用mysqldump备份数据时，大概执行步骤如下：</span><br><span class="line"><span class="number">180322</span> <span class="number">17</span>:<span class="number">39</span>:<span class="number">33</span>       <span class="number">7</span> Connect     root<span class="meta">@localhost</span> <span class="keyword">on</span></span><br><span class="line"><span class="number">7</span> Query       <span class="comment">/*!40100 SET @@SQL_MODE='' */</span></span><br><span class="line"><span class="number">7</span> Init DB     guo</span><br><span class="line"><span class="number">7</span> Query       SHOW TABLES LIKE <span class="string">'guo'</span></span><br><span class="line"><span class="number">7</span> Query       LOCK TABLES `guo` READ <span class="comment">/*!32311 LOCAL */</span></span><br><span class="line"><span class="number">7</span> Query       SET OPTION SQL_QUOTE_SHOW_CREATE=<span class="number">1</span></span><br><span class="line"><span class="number">7</span> Query       <span class="keyword">show</span> create table `guo`</span><br><span class="line"><span class="number">7</span> Query       <span class="keyword">show</span> fields from `guo`</span><br><span class="line"><span class="number">7</span> Query       <span class="keyword">show</span> table status like <span class="string">'guo'</span></span><br><span class="line"><span class="number">7</span> Query       SELECT <span class="comment">/*!40001 SQL_NO_CACHE */</span> * FROM `guo`</span><br><span class="line"><span class="number">7</span> Query       UNLOCK TABLES</span><br><span class="line"><span class="number">7</span> Quit</span><br><span class="line"></span><br><span class="line">其中，有一步是：<span class="keyword">show</span> fields from `guo`。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。</span><br><span class="line"></span><br><span class="line">所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。 </span><br><span class="line">（<span class="number">3</span>）配置方法</span><br><span class="line">key_buffer_size=<span class="number">64</span>M</span><br></pre></td></tr></table></figure>

<h2 id="query-cache-size"><a href="#query-cache-size" class="headerlink" title="query_cache_size ***"></a>query_cache_size ***</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br></pre></td><td class="code"><pre><span class="line">（1）简介：</span><br><span class="line">查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。</span><br><span class="line"></span><br><span class="line">SQL层：</span><br><span class="line">select * from t1 <span class="built_in">where</span> name=:NAME;</span><br><span class="line">select * from t1 <span class="built_in">where</span> name=:NAME;</span><br><span class="line"></span><br><span class="line">1、查询完结果之后，会对SQL语句进行<span class="built_in">hash</span>运算，得出<span class="built_in">hash</span>值,我们把他称之为SQL_ID</span><br><span class="line">2、会将存储引擎返回的结果+SQL_ID存储到缓存中。</span><br><span class="line"></span><br><span class="line">存储方式：</span><br><span class="line">例子：select * from t1  <span class="built_in">where</span> id=10;      100次</span><br><span class="line"></span><br><span class="line">1、将select * from t1  <span class="built_in">where</span> id=10; 进行<span class="built_in">hash</span>运算计算出一串<span class="built_in">hash</span>值，我们把它称之为“SQL_ID<span class="string">"</span></span><br><span class="line"><span class="string">2、将存储引擎返回上来的表的内容+SQLID存储到查询缓存中</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">使用方式：</span></span><br><span class="line"><span class="string">1、一条SQL执行时，进行hash运算，得出SQLID，去找query cache</span></span><br><span class="line"><span class="string">2、如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。</span></span><br><span class="line"><span class="string">注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">（2）判断依据</span></span><br><span class="line"><span class="string">mysql&gt; show status like "</span>%Qcache%<span class="string">";</span></span><br><span class="line"><span class="string">+-------------------------+---------+</span></span><br><span class="line"><span class="string">| Variable_name           | Value   |</span></span><br><span class="line"><span class="string">+-------------------------+---------+</span></span><br><span class="line"><span class="string">| Qcache_free_blocks      | 1       |</span></span><br><span class="line"><span class="string">| Qcache_free_memory      | 1031360 |</span></span><br><span class="line"><span class="string">| Qcache_hits             | 0       |</span></span><br><span class="line"><span class="string">| Qcache_inserts          | 0       |</span></span><br><span class="line"><span class="string">| Qcache_lowmem_prunes    | 0       |</span></span><br><span class="line"><span class="string">| Qcache_not_cached       | 2002    |</span></span><br><span class="line"><span class="string">| Qcache_queries_in_cache | 0       |</span></span><br><span class="line"><span class="string">| Qcache_total_blocks     | 1       |</span></span><br><span class="line"><span class="string">+-------------------------+---------+</span></span><br><span class="line"><span class="string">8 rows in set (0.00 sec)</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">---------------------状态说明--------------------</span></span><br><span class="line"><span class="string">Qcache_free_blocks：缓存中相邻内存块的个数。</span></span><br><span class="line"><span class="string">如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。</span></span><br><span class="line"><span class="string">注：当一个表被更新之后，和它相关的cache </span></span><br><span class="line"><span class="string">blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_lowmem_prunes：</span></span><br><span class="line"><span class="string">多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；</span></span><br><span class="line"><span class="string">Qcache_total_blocks：当前Query Cache 中的block 数量；。</span></span><br><span class="line"><span class="string">Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits) </span></span><br><span class="line"><span class="string">    90/         10000             0             90</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">如果出现hits比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据库</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">Qcache_free_blocks    来判断碎片</span></span><br><span class="line"><span class="string">Qcache_free_memory   +   Qcache_lowmem_prunes  来判断内存够不够</span></span><br><span class="line"><span class="string">Qcache_hits 多少次命中  Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)  </span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">（3）配置示例</span></span><br><span class="line"><span class="string">mysql&gt; show variables like '%query_cache%' ;</span></span><br><span class="line"><span class="string">+------------------------------+---------+</span></span><br><span class="line"><span class="string">| Variable_name                | Value   |</span></span><br><span class="line"><span class="string">+------------------------------+---------+</span></span><br><span class="line"><span class="string">| have_query_cache             | YES     |</span></span><br><span class="line"><span class="string">| query_cache_limit            | 1048576 |</span></span><br><span class="line"><span class="string">| query_cache_min_res_unit     | 4096    |</span></span><br><span class="line"><span class="string">| query_cache_size             | 1048576 |</span></span><br><span class="line"><span class="string">| query_cache_type             | OFF     |</span></span><br><span class="line"><span class="string">| query_cache_wlock_invalidate | OFF     |</span></span><br><span class="line"><span class="string">+------------------------------+---------+</span></span><br><span class="line"><span class="string">6 rows in set (0.00 sec)</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">mysql&gt; </span></span><br><span class="line"><span class="string">-------------------配置说明-------------------------------</span></span><br><span class="line"><span class="string">以上信息可以看出query_cache_type为off表示不缓存任何查询</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">各字段的解释：</span></span><br><span class="line"><span class="string">query_cache_limit：超过此大小的查询将不缓存</span></span><br><span class="line"><span class="string">query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。</span></span><br><span class="line"><span class="string">query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：</span></span><br><span class="line"><span class="string">如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。</span></span><br><span class="line"><span class="string">如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。</span></span><br><span class="line"><span class="string">如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string">修改/etc/my.cnf,配置完后的部分文件如下：</span></span><br><span class="line"><span class="string">query_cache_size=128M</span></span><br><span class="line"><span class="string">query_cache_type=1</span></span><br></pre></td></tr></table></figure>

<h2 id="max-connect-errors"><a href="#max-connect-errors" class="headerlink" title="max_connect_errors ***"></a>max_connect_errors ***</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。</span><br><span class="line">修改&#x2F;etc&#x2F;my.cnf文件，在[mysqld]下面添加如下内容</span><br><span class="line">max_connect_errors&#x3D;2000</span><br></pre></td></tr></table></figure>

<h2 id="sort-buffer-size"><a href="#sort-buffer-size" class="headerlink" title="sort_buffer_size ***"></a>sort_buffer_size ***</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介：</span><br><span class="line">每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速</span><br><span class="line">ORDER BY </span><br><span class="line">GROUP BY</span><br><span class="line">distinct</span><br><span class="line"><span class="keyword">union</span> </span><br><span class="line"></span><br><span class="line">（<span class="number">2</span>）配置依据</span><br><span class="line">Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。</span><br><span class="line">列如：<span class="number">500</span>个连接将会消耗<span class="number">500</span>*sort_buffer_size（<span class="number">2</span>M）=<span class="number">1</span>G内存</span><br><span class="line">（<span class="number">3</span>）配置方法</span><br><span class="line"> 修改/etc/my.cnf文件，在[mysqld]下面添加如下：</span><br><span class="line">sort_buffer_size=<span class="number">1</span>M</span><br></pre></td></tr></table></figure>

<h2 id="max-allowed-packet"><a href="#max-allowed-packet" class="headerlink" title="max_allowed_packet *****"></a>max_allowed_packet *****</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">（1）简介：</span><br><span class="line">mysql根据配置文件会限制，server接受的数据包大小。</span><br><span class="line">（2）配置依据：</span><br><span class="line">有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数</span><br><span class="line">（3）配置方法：</span><br><span class="line">max_allowed_packet&#x3D;32M</span><br></pre></td></tr></table></figure>

<h2 id="join-buffer-size"><a href="#join-buffer-size" class="headerlink" title="join_buffer_size ***"></a>join_buffer_size ***</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> a.name,b.name <span class="keyword">from</span> a <span class="keyword">join</span> b <span class="keyword">on</span> a.id=b.id <span class="keyword">where</span> xxxx</span><br><span class="line">用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。</span><br><span class="line">尽量在SQL与方面进行优化，效果较为明显。</span><br><span class="line">优化的方法：在<span class="keyword">on</span>条件列加索引，至少应当是有MUL索引</span><br></pre></td></tr></table></figure>

<h2 id="thread-cache-size"><a href="#thread-cache-size" class="headerlink" title="thread_cache_size  *****"></a>thread_cache_size  *****</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">(<span class="number">1</span>)简介</span><br><span class="line">服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.</span><br><span class="line">（<span class="number">2</span>）配置依据</span><br><span class="line">通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。</span><br><span class="line">设置规则如下：<span class="number">1</span>GB 内存配置为<span class="number">8</span>，<span class="number">2</span>GB配置为<span class="number">16</span>，<span class="number">3</span>GB配置为<span class="number">32</span>，<span class="number">4</span>GB或更高内存，可配置更大。</span><br><span class="line">服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)</span><br><span class="line"></span><br><span class="line">试图连接到MySQL(不管是否连接成功)的连接数</span><br><span class="line">mysql&gt;  show status like <span class="string">'threads_%'</span>;</span><br><span class="line">+-------------------+-------+</span><br><span class="line"><span class="params">| Variable_name     |</span> Value <span class="params">|</span></span><br><span class="line"><span class="params">+-------------------+-------+</span></span><br><span class="line"><span class="params">|</span> Threads_cached    <span class="params">| 8     |</span></span><br><span class="line"><span class="params">| Threads_connected |</span> <span class="number">2</span>     <span class="params">|</span></span><br><span class="line"><span class="params">|</span> Threads_created   <span class="params">| 4783  |</span></span><br><span class="line"><span class="params">| Threads_running   |</span> <span class="number">1</span>     <span class="params">|</span></span><br><span class="line"><span class="params">+-------------------+-------+</span></span><br><span class="line"><span class="params">4 rows <span class="keyword">in</span> set (0.00 sec)</span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。</span></span><br><span class="line"><span class="params">Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。</span></span><br><span class="line"><span class="params">Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。</span></span><br><span class="line"><span class="params">Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。</span></span><br><span class="line"><span class="params">(3)配置方法：</span></span><br><span class="line"><span class="params">thread_cache_size=32</span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">整理：</span></span><br><span class="line"><span class="params">Threads_created  ：一般在架构设计阶段，会设置一个测试值，做压力测试。</span></span><br><span class="line"><span class="params">结合zabbix监控，看一段时间内此状态的变化。</span></span><br><span class="line"><span class="params">如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。</span></span><br><span class="line"><span class="params">如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）</span></span><br></pre></td></tr></table></figure>

<h2 id="innodb-buffer-pool-size"><a href="#innodb-buffer-pool-size" class="headerlink" title="innodb_buffer_pool_size  *****"></a>innodb_buffer_pool_size  *****</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">（1）简介</span><br><span class="line">对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。</span><br><span class="line">（2）配置依据：</span><br><span class="line">InnoDB使用该参数指定大小的内存来缓冲数据和索引。</span><br><span class="line">对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。</span><br><span class="line">（3）配置方法</span><br><span class="line">innodb_buffer_pool_size&#x3D;2048M</span><br></pre></td></tr></table></figure>

<h2 id="innodb-flush-log-at-trx-commit"><a href="#innodb-flush-log-at-trx-commit" class="headerlink" title="innodb_flush_log_at_trx_commit **"></a>innodb_flush_log_at_trx_commit <strong>**</strong></h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">（1）简介</span><br><span class="line">主要控制了innodb将<span class="built_in">log</span> buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。</span><br><span class="line">0，表示当事务提交时，不做日志写入操作，而是每秒钟将<span class="built_in">log</span> buffer中的数据写入日志文件并flush磁盘一次；</span><br><span class="line">1，</span><br><span class="line">每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；</span><br><span class="line">2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。</span><br><span class="line"></span><br><span class="line">（2）配置依据</span><br><span class="line">实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。</span><br><span class="line">根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。</span><br><span class="line">（3）配置方法</span><br><span class="line">innodb_flush_log_at_trx_commit=1</span><br><span class="line">双1标准中的一个1</span><br></pre></td></tr></table></figure>

<h2 id="innodb-thread-concurrency"><a href="#innodb-thread-concurrency" class="headerlink" title="innodb_thread_concurrency  ***"></a>innodb_thread_concurrency  ***</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">（<span class="number">1</span>）简介</span><br><span class="line">此参数用来设置innodb线程的并发数量，默认值为<span class="number">0</span>表示不限制。</span><br><span class="line">（<span class="number">2</span>）配置依据</span><br><span class="line">在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：</span><br><span class="line">如果一个工作负载中，并发用户线程的数量小于<span class="number">64</span>，建议设置innodb_thread_concurrency=<span class="number">0</span>；</span><br><span class="line">如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=<span class="number">128</span>，</span><br><span class="line">并通过不断的降低这个参数，<span class="number">96</span>, <span class="number">80</span>, <span class="number">64</span>等等，直到发现能够提供最佳性能的线程数，</span><br><span class="line">例如，假设系统通常有<span class="number">40</span>到<span class="number">50</span>个用户，但定期的数量增加至<span class="number">60</span>，<span class="number">70</span>，甚至<span class="number">200</span>。你会发现，</span><br><span class="line">性能在<span class="number">80</span>个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，</span><br><span class="line">建议设置innodb_thread_concurrency参数为<span class="number">80</span>，以避免影响性能。</span><br><span class="line">如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如<span class="number">20</span>个虚拟CPU），</span><br><span class="line">建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），</span><br><span class="line">如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。</span><br><span class="line">但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，</span><br><span class="line">你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。</span><br><span class="line">在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。</span><br><span class="line">定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。</span><br><span class="line"></span><br><span class="line"><span class="number">128</span>   -----&gt; top  cpu  </span><br><span class="line">设置标准：</span><br><span class="line"><span class="number">1</span>、当前系统cpu使用情况，均不均匀</span><br><span class="line">top</span><br><span class="line"></span><br><span class="line"><span class="number">2</span>、当前的连接数，有没有达到顶峰</span><br><span class="line"><span class="keyword">show</span> status like <span class="string">'threads_%'</span>;</span><br><span class="line"><span class="keyword">show</span> processlist;</span><br><span class="line">（<span class="number">3</span>）配置方法：</span><br><span class="line">innodb_thread_concurrency=<span class="number">8</span></span><br><span class="line">方法:</span><br><span class="line">    <span class="number">1.</span> 看top ,观察每个cpu的各自的负载情况</span><br><span class="line">    <span class="number">2.</span> 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值</span><br><span class="line">    <span class="number">3.</span> 一直观察top状态,直到达到比较均匀时,说明已经到位了.</span><br></pre></td></tr></table></figure>

<h2 id="innodb-log-buffer-size"><a href="#innodb-log-buffer-size" class="headerlink" title="innodb_log_buffer_size"></a>innodb_log_buffer_size</h2><figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。</span><br><span class="line">innodb_log_buffer_size=<span class="number">128</span>M</span><br><span class="line"></span><br><span class="line">设定依据：</span><br><span class="line"><span class="number">1</span>、大事务： 存储过程调用 <span class="built_in">CALL</span></span><br><span class="line"><span class="number">2</span>、多事务</span><br></pre></td></tr></table></figure>

<h2 id="innodb-log-file-size-100M"><a href="#innodb-log-file-size-100M" class="headerlink" title="innodb_log_file_size = 100M    *****"></a>innodb_log_file_size = 100M    *****</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">设置 ib_logfile0  ib_logfile1 </span><br><span class="line">此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.</span><br><span class="line">innodb_log_file_size &#x3D; 100M</span><br></pre></td></tr></table></figure>

<h2 id="innodb-log-files-in-group-3"><a href="#innodb-log-files-in-group-3" class="headerlink" title="innodb_log_files_in_group = 3  *****"></a>innodb_log_files_in_group = 3  *****</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3</span><br></pre></td></tr></table></figure>

<h2 id="read-buffer-size-1M"><a href="#read-buffer-size-1M" class="headerlink" title="read_buffer_size = 1M  **"></a>read_buffer_size = 1M  **</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享</span><br></pre></td></tr></table></figure>

<h2 id="read-rnd-buffer-size-1M"><a href="#read-rnd-buffer-size-1M" class="headerlink" title="read_rnd_buffer_size = 1M **"></a>read_rnd_buffer_size = 1M **</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。</span><br><span class="line">注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。</span><br></pre></td></tr></table></figure>

<h2 id="bulk-insert-buffer-size-8M"><a href="#bulk-insert-buffer-size-8M" class="headerlink" title="bulk_insert_buffer_size = 8M **"></a>bulk_insert_buffer_size = 8M **</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">批量插入数据缓存大小，可以有效提高插入效率，默认为8M</span><br><span class="line">tokuDB    percona</span><br><span class="line">myrocks   </span><br><span class="line">RocksDB</span><br><span class="line">TiDB</span><br><span class="line">MongoDB</span><br></pre></td></tr></table></figure>

<h2 id="binary-log"><a href="#binary-log" class="headerlink" title="binary log  *****"></a>binary log  *****</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">log-bin=/<span class="keyword">data</span>/mysql-bin</span><br><span class="line">binlog_cache_size = <span class="number">2</span>M <span class="comment">//为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M</span></span><br><span class="line">max_binlog_cache_size = <span class="number">8</span>M <span class="comment">//表示的是binlog 能够使用的最大cache 内存大小</span></span><br><span class="line">max_binlog_size= <span class="number">512</span>M <span class="comment">//指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。</span></span><br><span class="line">expire_logs_days = <span class="number">7</span> <span class="comment">//定义了mysql清除过期日志的时间。</span></span><br><span class="line">二进制日志自动删除的天数。默认值为<span class="number">0</span>,表示“没有自动删除”。</span><br><span class="line">log-bin=/<span class="keyword">data</span>/mysql-bin</span><br><span class="line">binlog_format=row </span><br><span class="line">sync_binlog=<span class="number">1</span></span><br><span class="line">双<span class="number">1</span>标准(基于安全的控制)：</span><br><span class="line">sync_binlog=<span class="number">1</span>   什么时候刷新binlog到磁盘，每次事务commit</span><br><span class="line">innodb_flush_log_at_trx_commit=<span class="number">1</span></span><br><span class="line"><span class="keyword">set</span> sql_log_bin=<span class="number">0</span>;</span><br><span class="line">show status like <span class="string">'com_%'</span>;</span><br></pre></td></tr></table></figure>

<h2 id="安全参数"><a href="#安全参数" class="headerlink" title="安全参数  *****"></a>安全参数  *****</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">Innodb_flush_method=(O_DIRECT, fsync) </span><br><span class="line"><span class="number">1</span>、fsync    ：</span><br><span class="line">（<span class="number">1</span>）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘</span><br><span class="line">（<span class="number">2</span>）在<span class="keyword">redo</span> buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘</span><br><span class="line">但，如果innodb_flush_log_at_trx_commit=<span class="number">1</span>的话，日志还是直接每次commit直接写入磁盘</span><br><span class="line"><span class="number">2</span>、 Innodb_flush_method=O_DIRECT</span><br><span class="line">（<span class="number">1</span>）在数据页需要持久化时，直接写入磁盘</span><br><span class="line">（<span class="number">2</span>）在<span class="keyword">redo</span> buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘</span><br><span class="line">但，如果innodb_flush_log_at_trx_commit=<span class="number">1</span>的话，日志还是直接每次commit直接写入磁盘</span><br><span class="line"></span><br><span class="line">最安全模式：</span><br><span class="line">innodb_flush_log_at_trx_commit=<span class="number">1</span></span><br><span class="line">innodb_flush_method=O_DIRECT</span><br><span class="line">最高性能模式：</span><br><span class="line">innodb_flush_log_at_trx_commit=<span class="number">0</span></span><br><span class="line">innodb_flush_method=fsync</span><br><span class="line">        </span><br><span class="line">一般情况下，我们更偏向于安全。 </span><br><span class="line">“双一标准”</span><br><span class="line">innodb_flush_log_at_trx_commit=<span class="number">1</span>        ***************</span><br><span class="line">sync_binlog=<span class="number">1</span>                                   ***************</span><br><span class="line">innodb_flush_method=O_DIRECT</span><br></pre></td></tr></table></figure>

<h1 id="6-参数优化结果"><a href="#6-参数优化结果" class="headerlink" title="6. 参数优化结果"></a>6. 参数优化结果</h1><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">[mysqld]</span><br><span class="line">basedir&#x3D;&#x2F;data&#x2F;mysql</span><br><span class="line">datadir&#x3D;&#x2F;data&#x2F;mysql&#x2F;data</span><br><span class="line">socket&#x3D;&#x2F;tmp&#x2F;mysql.sock</span><br><span class="line">log-error&#x3D;&#x2F;var&#x2F;log&#x2F;mysql.log</span><br><span class="line">log_bin&#x3D;&#x2F;data&#x2F;binlog&#x2F;mysql-bin</span><br><span class="line">binlog_format&#x3D;row</span><br><span class="line">skip-name-resolve</span><br><span class="line">server-id&#x3D;52</span><br><span class="line">gtid-mode&#x3D;on</span><br><span class="line">enforce-gtid-consistency&#x3D;true</span><br><span class="line">log-slave-updates&#x3D;1</span><br><span class="line">relay_log_purge&#x3D;0</span><br><span class="line">max_connections&#x3D;1024</span><br><span class="line">back_log&#x3D;128</span><br><span class="line">wait_timeout&#x3D;60</span><br><span class="line">interactive_timeout&#x3D;7200</span><br><span class="line">key_buffer_size&#x3D;16M</span><br><span class="line">query_cache_size&#x3D;64M</span><br><span class="line">query_cache_type&#x3D;1</span><br><span class="line">query_cache_limit&#x3D;50M</span><br><span class="line">max_connect_errors&#x3D;20</span><br><span class="line">sort_buffer_size&#x3D;2M</span><br><span class="line">max_allowed_packet&#x3D;32M</span><br><span class="line">join_buffer_size&#x3D;2M</span><br><span class="line">thread_cache_size&#x3D;200</span><br><span class="line">innodb_buffer_pool_size&#x3D;1024M</span><br><span class="line">innodb_flush_log_at_trx_commit&#x3D;1</span><br><span class="line">innodb_log_buffer_size&#x3D;32M</span><br><span class="line">innodb_log_file_size&#x3D;128M</span><br><span class="line">innodb_log_files_in_group&#x3D;3</span><br><span class="line">binlog_cache_size&#x3D;2M</span><br><span class="line">max_binlog_cache_size&#x3D;8M</span><br><span class="line">max_binlog_size&#x3D;512M</span><br><span class="line">expire_logs_days&#x3D;7</span><br><span class="line">read_buffer_size&#x3D;2M</span><br><span class="line">read_rnd_buffer_size&#x3D;2M</span><br><span class="line">bulk_insert_buffer_size&#x3D;8M</span><br><span class="line">[client]</span><br><span class="line">socket&#x3D;&#x2F;tmp&#x2F;mysql.sock  </span><br><span class="line">        </span><br><span class="line">再次压力测试  ：</span><br><span class="line"> mysqlslap --defaults-file&#x3D;&#x2F;etc&#x2F;my.cnf --concurrency&#x3D;100 --iterations&#x3D;1 --create-schema&#x3D;&#39;oldboy&#39; --query&#x3D;&quot;select * from oldboy.t_100w where k2&#x3D;&#39;FGCD&#39;&quot; engine&#x3D;innodb --number-of-queries&#x3D;200000 -uroot -p123 -verbose</span><br></pre></td></tr></table></figure>

<h1 id="7-锁的监控及处理"><a href="#7-锁的监控及处理" class="headerlink" title="7. 锁的监控及处理"></a>7. 锁的监控及处理</h1><h2 id="7-1-锁等待模拟"><a href="#7-1-锁等待模拟" class="headerlink" title="7.1 锁等待模拟"></a>7.1 锁等待模拟</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">概念：</span><br><span class="line">Record Lock </span><br><span class="line">Next Lock </span><br><span class="line">GAP Lock</span><br><span class="line">X </span><br><span class="line">IX</span><br><span class="line">S </span><br><span class="line">IS</span><br></pre></td></tr></table></figure>

<h2 id="tx1"><a href="#tx1" class="headerlink" title="tx1:"></a>tx1:</h2><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> <span class="title">oldboy</span></span><br><span class="line"><span class="title">UPDATE</span> <span class="title">t_100w</span> <span class="title">SET</span> <span class="title">k1</span>='<span class="title">av</span>' <span class="title">WHERE</span> <span class="title">id</span>=10;</span><br><span class="line"><span class="comment">## tx2:</span></span><br><span class="line"><span class="keyword">USE</span> <span class="title">oldboy</span> </span><br><span class="line"><span class="title">UPDATE</span>  <span class="title">t_100w</span> <span class="title">SET</span> <span class="title">k1</span>='<span class="title">az</span>' <span class="title">WHERE</span> <span class="title">id</span>=10;</span><br></pre></td></tr></table></figure>

<h2 id="监控锁状态"><a href="#监控锁状态" class="headerlink" title="监控锁状态"></a>监控锁状态</h2><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-9e1bcd370862c1fa.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1200/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">## 1. 看有没有锁等待</span></span><br><span class="line">SHOW  STATUS LIKE <span class="string">'innodb_row_lock%'</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">## 2. 查看哪个事务在等待(被阻塞了)</span></span><br><span class="line"><span class="keyword">USE</span> <span class="title">information_schema</span></span><br><span class="line"><span class="title">SELECT</span> * <span class="title">FROM</span> <span class="title">information_schema</span>.<span class="title">INNODB_TRX</span> <span class="title">WHERE</span> <span class="title">trx_state</span>='<span class="title">LOCK</span> <span class="title">WAIT</span>';</span><br><span class="line">trx_id : 事务ID号</span><br><span class="line">trx_state : 当前事务的状态</span><br><span class="line">trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===&gt;Id或trx_id )</span><br><span class="line">trx_query : 当前被阻塞的操作(一般是要丢给开发的)</span><br></pre></td></tr></table></figure>

<h2 id="7-3-查看锁源-谁锁的我"><a href="#7-3-查看锁源-谁锁的我" class="headerlink" title="7.3.查看锁源,谁锁的我!"></a>7.3.查看锁源,谁锁的我!</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM sys.innodb_lock_waits;     <span class="comment">## ====&gt;被锁的和锁定它的之间关系</span></span><br><span class="line"></span><br><span class="line">locked_table : 哪张表出现的等待 </span><br><span class="line">waiting_trx_id: 等待的事务(与上个视图trx_id 对应)</span><br><span class="line">waiting_pid   : 等待的线程号(与上个视图trx_mysql_thread_id)</span><br><span class="line">blocking_trx_id : 锁源的事务ID </span><br><span class="line">blocking_pid    : 锁源的线程号</span><br></pre></td></tr></table></figure>

<h2 id="7-4-找到锁源的thread-id"><a href="#7-4-找到锁源的thread-id" class="headerlink" title="7.4. 找到锁源的thread_id"></a>7.4. 找到锁源的thread_id</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM performance_schema.threads WHERE processlist_id=<span class="number">15</span>;</span><br><span class="line">====&gt; <span class="number">41</span></span><br></pre></td></tr></table></figure>

<h2 id="7-5-找到锁源的SQL语句"><a href="#7-5-找到锁源的SQL语句" class="headerlink" title="7.5. 找到锁源的SQL语句"></a>7.5. 找到锁源的SQL语句</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">-- 当前在执行的语句</span><br><span class="line">SELECT * FROM performance_schema.<span class="string">`events_statements_current`</span> WHERE thread_id=<span class="number">41</span>;</span><br><span class="line">-- 执行语句的历史</span><br><span class="line">SELECT * FROM performance_schema.<span class="string">`events_statements_history`</span> WHERE thread_id=<span class="number">41</span>;</span><br><span class="line"></span><br><span class="line">得出结果,丢给开发</span><br><span class="line">表信息 </span><br><span class="line">被阻塞的</span><br><span class="line">锁源SQL</span><br><span class="line"></span><br><span class="line">练习:</span><br><span class="line">一键获得以上信息,请写出具体的SQL语句</span><br></pre></td></tr></table></figure>

<h2 id="7-6-优化项目-锁的监控及处理"><a href="#7-6-优化项目-锁的监控及处理" class="headerlink" title="7.6 优化项目:锁的监控及处理"></a>7.6 优化项目:锁的监控及处理</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span> 背景: </span><br><span class="line">硬件环境: DELL R720,E系列<span class="number">16</span>核,<span class="number">48</span>G MEM,SAS*<span class="number">900</span>G*<span class="number">6</span>,RAID10</span><br><span class="line">在例行巡检时,发现<span class="number">9</span><span class="number">-11</span>点时间段的CPU压力非常高(<span class="number">80</span><span class="number">-90</span>%)</span><br><span class="line"></span><br><span class="line"><span class="number">2.</span> 项目的职责</span><br><span class="line">    <span class="number">2.1</span> 通过top详细排查,发现mysqld进程占比达到了<span class="number">700</span><span class="number">-800</span>%</span><br><span class="line">    <span class="number">2.2</span> 其中有量的CPU是被用作的SYS和WAIT,us处于正常</span><br><span class="line">    <span class="number">2.3</span> 怀疑是MySQL 锁 或者SQL语句出了问题</span><br><span class="line">    <span class="number">2.4</span> 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句    </span><br><span class="line">    (<span class="number">1</span>) pt-query-diagest 查看慢日志  </span><br><span class="line">    (<span class="number">2</span>) 锁等待有没有?</span><br><span class="line">    db03 [(none)]&gt;show status like <span class="string">'innodb_row_lock%'</span>;</span><br><span class="line">    +-------------------------------+-------+</span><br><span class="line">    | Variable_name                 | Value |</span><br><span class="line">    +-------------------------------+-------+</span><br><span class="line">    | Innodb_row_lock_current_waits | <span class="number">0</span>     |</span><br><span class="line">    | Innodb_row_lock_time          | <span class="number">0</span>     |</span><br><span class="line">    | Innodb_row_lock_time_avg      | <span class="number">0</span>     |</span><br><span class="line">    | Innodb_row_lock_time_max      | <span class="number">0</span>     |</span><br><span class="line">    | Innodb_row_lock_waits         | <span class="number">0</span>     |</span><br><span class="line">    +-------------------------------+-------+</span><br><span class="line">    情况一:</span><br><span class="line">            有<span class="number">100</span>多个current_waits,说明当前很多锁等待情况</span><br><span class="line">    情况二:</span><br><span class="line">            <span class="number">1000</span>多个lock_waits,说明历史上发生过的锁等待很多</span><br><span class="line">    <span class="number">2.5</span> 查看那个事务在等待(被阻塞了)</span><br><span class="line">    <span class="number">2.6</span> 查看锁源事务信息(谁锁的我)</span><br><span class="line">    <span class="number">2.7</span> 找到锁源的thread_id </span><br><span class="line">    <span class="number">2.8</span> 找到锁源的SQL语句</span><br><span class="line"><span class="number">3.</span> 找到语句之后,和应用开发人员进行协商   </span><br><span class="line">    (<span class="number">1</span>)</span><br><span class="line">    开发人员描述,此语句是事务挂起导致</span><br><span class="line">    我们提出建议是临时kill 会话,最终解决问题</span><br><span class="line">    (<span class="number">2</span>) </span><br><span class="line">    开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待</span><br><span class="line">    临时解决方案,将阻塞事务的会话kill掉.</span><br><span class="line">    最终解决方案,修改代码中的业务逻辑</span><br><span class="line">项目结果:</span><br><span class="line">    经过排查处理,锁等待的个数减少<span class="number">80</span>%.解决了CPU持续峰值的问题.</span><br><span class="line">    </span><br><span class="line">锁监控设计到的命令:</span><br><span class="line">show status like <span class="string">'innodb_rows_lock%'</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> information_schema.innodb_trx;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> sys.innodb_lock_waits;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> performance_schema.threads;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> performance_schema.events_statements_current;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> performance_schema.events_statements_history;</span><br></pre></td></tr></table></figure>

<h2 id="7-7-死锁监控"><a href="#7-7-死锁监控" class="headerlink" title="7.7 死锁监控"></a>7.7 死锁监控</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> engine innodb status\G</span><br><span class="line"><span class="keyword">show</span> variables like <span class="string">'%deadlock%'</span>;</span><br><span class="line">vim /etc/my.cnf </span><br><span class="line">innodb_print_all_deadlocks = <span class="number">1</span></span><br></pre></td></tr></table></figure>

<h1 id="8-主从优化："><a href="#8-主从优化：" class="headerlink" title="8. 主从优化："></a>8. 主从优化：</h1><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">## <span class="number">5.7</span> 从库多线程MTS</span><br><span class="line">基本要求:</span><br><span class="line"><span class="number">5.7</span>以上的版本(忘记小版本)</span><br><span class="line">必须开启GTID </span><br><span class="line">binlog必须是row模式  </span><br><span class="line"></span><br><span class="line">gtid_mode=ON</span><br><span class="line">enforce_gtid_consistency=ON</span><br><span class="line">log_slave_updates=ON</span><br><span class="line">slave-parallel-<span class="keyword">type</span>=LOGICAL_CLOCK</span><br><span class="line">slave-parallel-workers=<span class="number">16</span></span><br><span class="line">master_info_repository=TABLE</span><br><span class="line">relay_log_info_repository=TABLE</span><br><span class="line">relay_log_recovery=ON</span><br><span class="line"></span><br><span class="line"><span class="number">5.7</span> :</span><br><span class="line">slave-parallel-<span class="keyword">type</span>=LOGICAL_CLOCK</span><br><span class="line">slave-parallel-workers=<span class="number">8</span></span><br><span class="line">cpu核心数作为标准</span><br><span class="line"></span><br><span class="line">CHANGE MASTER TO</span><br><span class="line">  MASTER_HOST=<span class="string">'10.0.0.128'</span>,</span><br><span class="line">  MASTER_USER=<span class="string">'repl'</span>,</span><br><span class="line">  MASTER_PASSWORD=<span class="string">'123'</span>,</span><br><span class="line">  MASTER_PORT=<span class="number">3307</span>,</span><br><span class="line">  MASTER_AUTO_POSITION=<span class="number">1</span>;</span><br><span class="line">start slave;</span><br></pre></td></tr></table></figure>

<p>=====================MySQL END==================</p>
<p>作者：wwwoldguocom<br>链接：<a href="https://www.jianshu.com/p/5dbb5e095c9a" target="_blank" rel="noopener">https://www.jianshu.com/p/5dbb5e095c9a</a><br>来源：简书<br>著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。</p>
</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/db/MySQL%E7%B3%BB%E5%88%97/04-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-%E7%B4%A2%E5%BC%95%E5%8F%8A%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92/">
        <span class="nav-arrow">← </span>
        
          db/MySQL系列/04-MySQL系列之-索引及执行计划
        
      </a>
    
    
      <a class="nav-right" href="/db/MySQL%E7%B3%BB%E5%88%97/01-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-MySQL%E4%BB%8B%E7%BB%8D%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE/">
        
          db/MySQL系列/01-MySQL系列之-MySQL介绍安装配置
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#1-优化哲学"><span class="toc-nav-text">1.优化哲学</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-1-为什么优化？"><span class="toc-nav-text">1.1 为什么优化？</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-2-优化风险"><span class="toc-nav-text">1.2 优化风险</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-3-谁参与优化"><span class="toc-nav-text">1.3 谁参与优化</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-4-优化方向"><span class="toc-nav-text">1.4 优化方向</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-5-优化的范围及思路"><span class="toc-nav-text">1.5 优化的范围及思路</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#优化效果和成本的评估："><span class="toc-nav-text">优化效果和成本的评估：</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#2-优化工具的使用"><span class="toc-nav-text">2. 优化工具的使用</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-系统层面的"><span class="toc-nav-text">2.1 系统层面的</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-1-1-CPU"><span class="toc-nav-text">2.1.1 CPU</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#top"><span class="toc-nav-text">top</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#cpu使用情况的平均值："><span class="toc-nav-text">cpu使用情况的平均值：</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#CPU每个核心的分别使用的情况（按1）："><span class="toc-nav-text">CPU每个核心的分别使用的情况（按1）：</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#程序是如何使用CPU的？"><span class="toc-nav-text">程序是如何使用CPU的？</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#CPU有效工作时间"><span class="toc-nav-text">CPU有效工作时间?</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#CPU无效工作时间"><span class="toc-nav-text">CPU无效工作时间</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#CPU各项指标说明"><span class="toc-nav-text">CPU各项指标说明</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-2-MEM"><span class="toc-nav-text">2.1.2 MEM</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-1-3-内存管理子系统："><span class="toc-nav-text">2.1.3 内存管理子系统：</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-4-swap"><span class="toc-nav-text">2.1.4 swap</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-2-iostat-命令"><span class="toc-nav-text">2.2. iostat 命令</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-3-数据库优化工具"><span class="toc-nav-text">2.3 数据库优化工具</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#3-优化思路分解"><span class="toc-nav-text">3. 优化思路分解</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-硬件优化"><span class="toc-nav-text">3.1 硬件优化</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#主机"><span class="toc-nav-text">主机</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#CPU根据数据库类型"><span class="toc-nav-text">CPU根据数据库类型</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#内存"><span class="toc-nav-text">内存</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#磁盘选择"><span class="toc-nav-text">磁盘选择</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#存储"><span class="toc-nav-text">存储</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#网络"><span class="toc-nav-text">网络</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-2-操作系统优化"><span class="toc-nav-text">3.2 操作系统优化</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#Swap调整"><span class="toc-nav-text">Swap调整</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#IO调度策略"><span class="toc-nav-text">IO调度策略</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-3-应用端"><span class="toc-nav-text">3.3 应用端</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#4-MySQL参数优化测试"><span class="toc-nav-text">4. MySQL参数优化测试</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#5-优化细节："><span class="toc-nav-text">5. 优化细节：</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-1-参数优化"><span class="toc-nav-text">5.1 参数优化</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#Max-connections"><span class="toc-nav-text">Max_connections *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#back-log"><span class="toc-nav-text">back_log ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#wait-timeout和interactive-timeout"><span class="toc-nav-text">wait_timeout和interactive_timeout ****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-4-key-buffer-size"><span class="toc-nav-text">5.4 key_buffer_size *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#query-cache-size"><span class="toc-nav-text">query_cache_size ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#max-connect-errors"><span class="toc-nav-text">max_connect_errors ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#sort-buffer-size"><span class="toc-nav-text">sort_buffer_size ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#max-allowed-packet"><span class="toc-nav-text">max_allowed_packet *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#join-buffer-size"><span class="toc-nav-text">join_buffer_size ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#thread-cache-size"><span class="toc-nav-text">thread_cache_size  *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-buffer-pool-size"><span class="toc-nav-text">innodb_buffer_pool_size  *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-flush-log-at-trx-commit"><span class="toc-nav-text">innodb_flush_log_at_trx_commit **</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-thread-concurrency"><span class="toc-nav-text">innodb_thread_concurrency  ***</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-log-buffer-size"><span class="toc-nav-text">innodb_log_buffer_size</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-log-file-size-100M"><span class="toc-nav-text">innodb_log_file_size &#x3D; 100M    *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#innodb-log-files-in-group-3"><span class="toc-nav-text">innodb_log_files_in_group &#x3D; 3  *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#read-buffer-size-1M"><span class="toc-nav-text">read_buffer_size &#x3D; 1M  **</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#read-rnd-buffer-size-1M"><span class="toc-nav-text">read_rnd_buffer_size &#x3D; 1M **</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#bulk-insert-buffer-size-8M"><span class="toc-nav-text">bulk_insert_buffer_size &#x3D; 8M **</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#binary-log"><span class="toc-nav-text">binary log  *****</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#安全参数"><span class="toc-nav-text">安全参数  *****</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#6-参数优化结果"><span class="toc-nav-text">6. 参数优化结果</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#7-锁的监控及处理"><span class="toc-nav-text">7. 锁的监控及处理</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-1-锁等待模拟"><span class="toc-nav-text">7.1 锁等待模拟</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#tx1"><span class="toc-nav-text">tx1:</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#监控锁状态"><span class="toc-nav-text">监控锁状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-3-查看锁源-谁锁的我"><span class="toc-nav-text">7.3.查看锁源,谁锁的我!</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-4-找到锁源的thread-id"><span class="toc-nav-text">7.4. 找到锁源的thread_id</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-5-找到锁源的SQL语句"><span class="toc-nav-text">7.5. 找到锁源的SQL语句</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-6-优化项目-锁的监控及处理"><span class="toc-nav-text">7.6 优化项目:锁的监控及处理</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-7-死锁监控"><span class="toc-nav-text">7.7 死锁监控</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#8-主从优化："><span class="toc-nav-text">8. 主从优化：</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/db/MySQL系列/13-MySQL系列之-优化/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "db/MySQL系列/13-MySQL系列之-优化",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>